-- 销售费用报表
alter proc proc_czly_SaleFeeRpt(
    @date datetime,
    @orgNo varchar(55)
) as
begin
set nocount on


declare @year int = Year(@date)
declare @orgId int=(select FOrgId from T_ORG_ORGANIZATIONS where FNumber=@orgNo)

select * into #sale from dbo.fun_czly_GetFeeData(@year, '6601', @orgId) order by 序号


-- 财务费用通过科目汇总
select a.FNumber, al.FName, v.FPeriod, ve.FDEBIT, ve.FCREDIT
into #t_finance
from V_CN_VOUCHER v
inner join V_CN_VOUCHERENTRY ve on ve.FVOUCHERID=v.FVOUCHERID
inner join T_BD_ACCOUNT a on a.FACCTID=ve.FACCOUNTID
inner join T_BD_ACCOUNT_L al on al.FACCTID=ve.FACCOUNTID
where a.FNumber like '6603%' and FYear=@year and v.FACCTORGID=@orgId
and dbo.fun_czly_IsCarryForward(v.FVOUCHERID)=0

select FName 费用项目,
    sum(FJanFee) 一月, sum(FFebFee) 二月, sum(FMarFee) 三月, sum(FAprFee) 四月,
    sum(FMayFee) 五月, sum(FJunFee) 六月, sum(FJulFee) 七月, sum(FAugFee) 八月,
    sum(FSeptFee) 九月, sum(FOctFee) 十月, sum(FNovFee) 十一月, sum(FDecFee) 十二月,
    sum(FTotal) 累计
into #finance
from (
    select FNumber, FName,
            sum(FDEBIT)-sum(FCREDIT) FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=1
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, sum(FDEBIT)-sum(FCREDIT) FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=2
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, sum(FDEBIT)-sum(FCREDIT) FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=3
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, sum(FDEBIT)-sum(FCREDIT) FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=4
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            sum(FDEBIT)-sum(FCREDIT) FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=5
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, sum(FDEBIT)-sum(FCREDIT) FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=6
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, sum(FDEBIT)-sum(FCREDIT) FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=7
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, sum(FDEBIT)-sum(FCREDIT) FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=8
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            sum(FDEBIT)-sum(FCREDIT) FSeptFee, 0 FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=9
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, sum(FDEBIT)-sum(FCREDIT) FOctFee, 0 FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=10
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, sum(FDEBIT)-sum(FCREDIT) FNovFee, 0 FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=11
    group by FNumber, FName
    union all
    select FNumber, FName,
            0 FJanFee, 0 FFebFee, 0 FMarFee, 0 FAprFee,
            0 FMayFee, 0 FJunFee, 0 FJulFee, 0 FAugFee,
            0 FSeptFee, 0 FOctFee, 0 FNovFee, sum(FDEBIT)-sum(FCREDIT) FDecFee, 
            sum(FDEBIT)-sum(FCREDIT)  FTotal
    from #t_finance where FPeriod=12
    group by FNumber, FName
) t
group by FNumber, FName
order by FNumber

-- 1月份上手期初财务数据
if @year = 2021 and @orgId = 100008
begin
    update #finance set 一月=3081.13 where 费用项目='手续费'
end


select * from #sale
union all
select 0, '营业费用合计' 费用项目, 
    sum(一月) 一月, sum(二月) 二月, sum(三月) 三月, sum(四月) 四月,
    sum(五月) 五月, sum(六月) 六月, sum(七月) 七月, sum(八月) 八月,
    sum(九月) 九月, sum(十月) 十月, sum(十一月) 十一月, sum(十二月) 十二月,
    sum(累计) 累计
from #sale
union all
select 0, * from #finance
union all
select 0, '财务费用合计', 
    sum(一月) 一月, sum(二月) 二月, sum(三月) 三月, sum(四月) 四月,
    sum(五月) 五月, sum(六月) 六月, sum(七月) 七月, sum(八月) 八月,
    sum(九月) 九月, sum(十月) 十月, sum(十一月) 十一月, sum(十二月) 十二月,
    sum(累计) 累计
from #finance


end

-- exec proc_czly_SaleFeeRpt @date='2021-1-1', @orgNo='SS'

